package cn.minimelon.solon.controller.excel;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.minimelon.solon.domain.excel.PurContact;
import cn.minimelon.solon.utils.Excel2007ReadUtils;
import lombok.extern.slf4j.Slf4j;
import org.noear.solon.annotation.Controller;
import org.noear.solon.annotation.Get;
import org.noear.solon.annotation.Mapping;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.util.*;

@Slf4j
@Controller
@Mapping("/excel")
public class ExcelReadController {

    @Get
    @Mapping("/read/contract")
    public String readExcel() {
        Map<String, String> nameIdMap = new HashMap<>();
        {
            String fileName1 = "/opt/logs/temp/hechuang/relation.xlsx";
            InputStream f1Stream = FileUtil.getInputStream(fileName1);
            // sheet: 0开始 row: 0开始
            List<Map<Integer, String>> list = Excel2007ReadUtils.readExcels(f1Stream, 0, 1);
            if (CollUtil.isNotEmpty(list)) {
                for (Map<Integer, String> row : list) {
                    nameIdMap.put(row.get(1), row.get(0));
                    nameIdMap.put(row.get(3), row.get(2));
                }
            }
        }

        int rowNum = 1;
        List<String> sqllist = new ArrayList<>();
        {
            String fileName2 = "/opt/logs/temp/hechuang/111.xlsx";
            InputStream f2Stream = FileUtil.getInputStream(fileName2);
            List<Map<Integer, String>> list = Excel2007ReadUtils.readExcels(f2Stream, 0, 1);
            if (CollUtil.isNotEmpty(list)) {
                for (Map<Integer, String> row : list) {
                    sqllist.add(buildPurContact(row, rowNum, nameIdMap));
                    rowNum++;
                }
            }
        }

        {
            String fileName3 = "/opt/logs/temp/hechuang/222.xlsx";
            InputStream f3Stream = FileUtil.getInputStream(fileName3);
            List<Map<Integer, String>> list = Excel2007ReadUtils.readExcels(f3Stream, 0, 1);
            if (CollUtil.isNotEmpty(list)) {
                for (Map<Integer, String> row : list) {
                    sqllist.add(buildPurContact(row, rowNum, nameIdMap));
                    rowNum++;
                }
            }
        }

        {
            try {
                String fileName = "/opt/logs/temp/hechuang/3333.sql";
                FileOutputStream fos = new FileOutputStream(fileName);
                BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(fos));
                for (String sql : sqllist) {
                    bw.write(sql);
                    bw.newLine();
                }
                bw.close();
                fos.close();
            } catch (Exception ex) {
                log.error("write error", ex);
            }
        }
        return "Solon欢迎您，请支持开源中国。";
    }

    private String buildPurContact(Map<Integer, String> row, int rowNum, Map<String, String> nameIdMap) {
        Date now = DateUtil.date();
        PurContact contact = new PurContact();
        contact.setId("h235eHT20251015X" + String.format("%04d", rowNum));
        contact.setContractCode(row.get(6));
        contact.setContractName(row.get(5));
        contact.setContractType(1);
        contact.setCollectorId("p1fee3");
        contact.setCollectorName("国药器械（重庆）科技发展有限公司");
        contact.setHosId("h235e");
        contact.setHosName("重庆市合川区人民医院");
        contact.setProvId(nameIdMap.get(row.get(3)));
        contact.setProvName(row.get(3));
        contact.setTaxRate(new BigDecimal(row.get(4)));
        contact.setBeginDate(DateUtil.parseDate(row.get(7)));
        contact.setEndDate(DateUtil.parseDate(row.get(8)));
        contact.setFillDate(now);
        contact.setLastUpdateDatetime(now);
        if (StrUtil.isEmpty(contact.getProvId()) || StrUtil.isEmpty(contact.getCollectorId())) {
            contact.setProvId(StrUtil.DASHED);
            log.error("rowNum: {} provName:{}", rowNum, contact.getProvName());
        }
        
        String template = "INSERT INTO `pur_contract` (" +
                "  `id`, `contract_type`, `contract_code`, `contract_name`, `hos_id`, `branch_id`, `collector_id`, `prov_id`, `tax_rate`, " +
                "  `sign_date`, `begin_date`, `end_date`, `status`, `filler`, `filler_name`, `fill_date`," +
                " `flag`, `version`, `last_update_datetime`, `hos_version`) " +
                "VALUES (" +
                "  '{id}', 1, '{contractCode}', '{contractName}', '{hosId}', NULL, '{collectorId}', '{provId}', {taxRate}, " +
                "  '2025-09-01 00:00:00.000', '{beginDate}', '{endDate}', 0, '0000', '超级管理员', '{fillDate}'," +
                "  1, 0, '{lastUpdateDatetime}', 3" +
                ");";
        return StrUtil.format(template, BeanUtil.beanToMap(contact));
    }
}
